/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package jdh;

import java.sql.*;
import java.text.SimpleDateFormat;

/**
 *
 * @author tale
 */
public class Do {
    
    public static Backend backend;
    static String[] tables = {"ADMINS", "TEACHERS", "STUDENTS"};

    public static void setBackend(Backend server){
        backend = server;
    }

    public static String id2Name(String id, int type){
        String name = null;
        Statement st = null;
        String queryStudentName =
                String.format("SELECT NAME FROM %s WHERE ID = '%s'",
                tables[type], name);
        try {
            st = backend.getConnection().createStatement();
            ResultSet rs = st.executeQuery(queryStudentName);
            if (rs.next())
                id = rs.getString("NAME");
        } catch (Exception e) {
            System.out.println("Do.id2Name: " + e.toString());
        } finally{
            try{ if (st != null) st.close();}catch(SQLException e){}
            return name;
        }

    }

    public static String name2ID(String table, String name){
        String id = null;
        Statement st = null;
        String queryStudentID =
                String.format("SELECT ID FROM %s WHERE NAME = '%s'", table, name);
        try {
            //System.out.println(queryStudentID);
            st = backend.getConnection().createStatement();
            ResultSet rs = st.executeQuery(queryStudentID);
            if (rs.next())
                id = rs.getString("ID");
        } catch (Exception e) {
            System.out.println("Do.name2ID: " + e.toString());
        } finally{
            try{ if (st != null) st.close();}catch(SQLException e){}
            return id;
        }
    }
    
    private static void executeUpdate(String sql){
        Statement st = null;
        System.out.println("Do: " + sql);
        try {
            st = backend.getConnection().createStatement();
            st.executeUpdate(sql);
        } catch (SQLException e) {
            System.out.println("Do: " + e.toString());
        } finally{
            try{ if (st != null) st.close();}catch(SQLException e){}
        }
    }

    public static void addLearning(String planID, String studentName){
        String studentID = name2ID("STUDENTS", studentName);
        if (studentID == null) return;
        String insert = String.format("INSERT INTO " +
                " LEARNINGS(PLANID, STUDENTID, REGISTER) VALUES('%s', '%s', '')",
                planID, studentID);
        executeUpdate(insert);
    }

    public static void deleteLearning(String planID, String studentName){
        String studentID = name2ID("STUDENTS", studentName);
        if (studentID == null) return;
        String delete = String.format("DELETE FROM LEARNINGS " +
                " WHERE PLANID = '%s' AND STUDENTID = '%s'",
                planID, studentID);
        executeUpdate(delete);
    }

    public static void addClass(String planName, String lectureName, String teacherName){
        String planID = name2ID("PLANS", planName);
        String lectureID = name2ID("LECTURES", lectureName);
        String teacherID = name2ID("TEACHERS", teacherName);
        if (planID == null || lectureID == null || teacherID == null) return;

        String insert = String.format("INSERT INTO " +
                " CLASSES(PLANID, LECTUREID, TEACHERID) VALUES('%s', '%s', '%s')",
                planID, lectureID, teacherID);
        executeUpdate(insert);
    }
    
    public static void deleteClass(String planName, String lectureName, String teacherName){
        String planID = name2ID("PLANS", planName);
        String lectureID = name2ID("LECTURES", lectureName);
        String teacherID = name2ID("TEACHERS", teacherName);
        if (planID == null || lectureID == null || teacherID == null) return;

        String delete = String.format("DELETE FROM CLASSES " +
                " WHERE PLANID = '%s' AND LECTUREID = '%s' AND TEACHERID = '%s'",
                planID, lectureID, teacherID);
        executeUpdate(delete);
    }
    

    public static void addPlan(){
        String insert = String.format("INSERT INTO PLANS(NAME) VALUES('NEW PLAN')");
        executeUpdate(insert);
    }

    public static void deletePlan(String planID){
        String delete = String.format("DELETE FROM PLANS " +
                " WHERE ID = '%s'",
                planID);
        executeUpdate(delete);
    }

    public static void markFromStudent(String lectureName, String teacherName, int mark){
        String lectureID = name2ID("LECTURES", lectureName);
        String teacherID = name2ID("TEACHERS", teacherName);
        if (lectureID == null || teacherID == null) return;

        String update = String.format("UPDATE MARKS " +
                "SET TEACHERMARK = '%S' " +
                "WHERE MARKS.LECTUREID = '%S' " +
                "AND MARKS.TEACHERID = '%S' ",
                mark, lectureID, teacherID);
        executeUpdate(update);
    }

    public static void markFromTeacher(String lectureName, String studentName, int mark){
        String lectureID = name2ID("LECTURES", lectureName);
        String studentID = name2ID("STUDENTS", studentName);
        if (lectureID == null || studentID == null) return;

        String update = String.format("UPDATE MARKS " +
                "SET STUDENTMARK = '%S' " +
                "WHERE MARKS.LECTUREID = '%S' " +
                "AND MARKS.STUDENTID = '%S' ",
                mark, lectureID, studentID);
        executeUpdate(update);
    }
    
    public static void addClassToPlan(String planID, String lectureName, String teacherName){
        String lectureID = name2ID("LECTURES", lectureName);
        String teacherID = name2ID("TEACHERS", teacherName);
         if (lectureID == null || teacherID == null) return;

        String update = String.format("UPDATE CLASSES " +
                "SET PLANID = '%S' " +
                "WHERE PLANID = '1' " +
                "AND LECTUREID = '%S' " +
                "AND TEACHERID = '%S' ",
                planID, lectureID, teacherID);
        executeUpdate(update);
    }

    public static void deleteClassFromPlan(String planID, String lectureName, String teacherName){
        String lectureID = name2ID("LECTURES", lectureName);
        String teacherID = name2ID("TEACHERS", teacherName);
         if (lectureID == null || teacherID == null) return;

        String update = String.format("UPDATE CLASSES " +
                "SET PLANID = '1' " +
                "WHERE PLANID = '%S' " +
                "AND LECTUREID = '%S' " +
                "AND TEACHERID = '%S' ",
                planID, lectureID, teacherID);
        executeUpdate(update);
    }

    public static void registerPlan(String studentID, String planName){
        String planID = name2ID("PLANS", planName);
        if (planID == null) return;
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String now = df.format(java.util.Calendar.getInstance().getTime());
        String update = String.format("UPDATE LEARNINGS " +
                "SET REGISTER = '%S' " +
                "WHERE STUDENTID = '%S' " +
                "AND PLANID = '%S'", now, studentID, planID);
        executeUpdate(update);
    }

    public static void addDependence(String firstName, String secondID){
        String firstID = name2ID("LECTURES", firstName);
        if (firstID == null) return;
        String insert = String.format("INSERT INTO " +
                "DEPENDENCES(FIRST, SECOND) " +
                "VALUES('%S', '%S')", firstID, secondID);
        executeUpdate(insert);
    }

    public static void deleteDependence(String firstName, String secondID){
        String firstID = name2ID("LECTURES", firstName);
        if (firstID == null) return;
        String delete = String.format("DELETE DEPENDENCES " +
                "WHERE FIRST = '%S' AND SECOND = '%S'", firstID, secondID);
        executeUpdate(delete);
    }

}
